﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SQLite;
using CNShapes;
using CNStreaming;
using Amib.Threading;
using CNUtility;
using BobcatUtility;

namespace CNStorage
{
    /// <summary>
    /// Manages storage of drawing data to a database. This includes the session,
    /// page and shape information.
    /// </summary><remarks>
    /// Singleton
    /// ---------
    /// This class is a singleton, and it observes and updates the singleton
    /// Session object to store and load data.
    /// 
    /// Tables
    /// ------
    /// The table structure mirrors the structure of the main objects in the 
    /// application. The main tables are 'Sessions', 'Pages' and 'Shapes'. There
    /// may be multiple pages for each session, and multiple shapes on each page.
    /// 
    /// </remarks>
    public class DBStorage
    {
        // TODO: Add ability to export/import a session to its own (DB) file.

        #region Public methods

        /// <summary>
        /// Singleton's getInstance method.
        /// </summary>
        public static DBStorage getInstance()
        {
            if (m_Instance == null)
            {
                m_Instance = new DBStorage();
            }
            return m_Instance;
        }

        /// <summary>
        /// Sets up the database.
        /// </summary>
        public void initialize()
        {
            // We create the DB (if it doesn't already exist)...
            createDB();

            // We open our connection to the DB...
            string strConnectionString = getDBConnectionString();
            m_Connection = new SQLiteConnection(strConnectionString);
            m_Connection.DefaultTimeout = 10;
            m_Connection.Open();
            m_WorkerThreadConnection = (SQLiteConnection)m_Connection.Clone();

            // We observe the active session, so that we can store data in the
            // DB when changes are made...
            Session activeSession = Session.getInstance();
            activeSession.NewSessionEvent += onNewSession;
            activeSession.PageAddedEvent += onPageAdded;
            activeSession.PageRemovedEvent += onPageRemoved;
            activeSession.ShapeEditCompletedEvent += onStoreShape;
            activeSession.ShapeAddedEvent += onStoreShape;
            activeSession.ShapeRemovedEvent += onRemoveShape;

            // TODO: observe events when the session's name or tag changes.
            // TODO: observe events when the page's name or tag changes.
        }

        /// <summary>
        /// Closes the DB and performs other cleanup.
        /// </summary>
        public void close()
        {
            // We make sure that the background thread has finished processing
            // before closing the DB...
            m_ThreadPool.WaitForIdle(WAIT_FOR_IDLE_TIMEOUT);

            // We remove any empty pages and sessions from the DB...
            removeEmptyPages();

            // We close the connections...
            if (m_Connection != null)
            {
                m_Connection.Close();
            }
            if (m_WorkerThreadConnection != null)
            {
                m_WorkerThreadConnection.Close();
            }

            // We unregister from events we are connected to...
            Session activeSession = Session.getInstance();
            activeSession.NewSessionEvent -= onNewSession;
            activeSession.PageAddedEvent -= onPageAdded;
            activeSession.PageRemovedEvent -= onPageRemoved;
            activeSession.ShapeEditCompletedEvent -= onStoreShape;
            activeSession.ShapeAddedEvent -= onStoreShape;
            activeSession.ShapeRemovedEvent -= onRemoveShape;
        }

        /// <summary>
        /// Loads the session with the ID passed in.
        /// </summary>
        public void loadSession(string strSessionID)
        {
            // We start a new session and load its pages...
            readSession(strSessionID);
            loadPagesIntoSession(strSessionID);
        }

        /// <summary>
        /// Finds the most recent session and loads it. Returns true if we
        /// loaded a session, false if we did not.
        /// </summary>
        public bool loadMostRecentSession()
        {
            bool bLoaded = false;
            SQLiteDataReader reader = null;
            try
            {
                // We select sessions ordered by creation time, and get the first one
                // (if there is one)...
                string strSQL = "SELECT ID FROM Sessions ORDER BY CreationDateTime DESC";
                reader = execute(m_Connection, strSQL);
                if (reader.Read() == true)
                {
                    string strSessionID = reader.GetString(0);
                    loadSession(strSessionID);
                    bLoaded = true;
                }
            }
            catch (Exception ex)
            {
                Logger.log(ex.Message, Logger.Level.WARN);
            }
            finally
            {
                if (reader != null) reader.Close();
            }

            return bLoaded;
        }

        /// <summary>
        /// Loads the shapes for the page passed in.
        /// </summary>
        public void loadShapesAsync(Page page)
        {
            QueuedAction action = new QueuedAction { Page = page };
            m_ThreadPool.QueueWorkItem(processLoadShapesAction, action);
        }

        /// <summary>
        /// Loads all the pages in the DB, but without loading their shapes, i.e. only
        /// the information in the Pages table is returned. The pages are loaded in
        /// order of creation date/time.
        /// </summary>
        public IList<Page> loadAllPagesWithoutShapes()
        {
            IList<Page> results = new List<Page>();
            SQLiteDataReader reader = null;
            try
            {
                string strSQL = "SELECT ID, SessionID, CreationDateTime, Name, Tag " +
                                "FROM Pages ORDER BY CreationDateTime ASC";
                reader = execute(m_Connection, strSQL);
                while (reader.Read() == true)
                {
                    string strPageID = reader.GetString(0);
                    Page page = new Page(new Guid(strPageID));

                    string strSessionID = reader.GetString(1);
                    page.SessionID = new Guid(strSessionID);

                    page.CreationDateTime = DateTime.FromOADate(reader.GetDouble(2));
                    page.Name = reader.GetString(3);
                    page.Tag = reader.GetString(4);

                    results.Add(page);
                }
            }
            catch (Exception ex)
            {
                Logger.log(ex.Message, Logger.Level.WARN);
            }
            finally
            {
                if (reader != null) reader.Close();
            }

            return results;
        }

		/// <summary>
		/// Returns the session ID for the page whose ID was passed in.
		/// </summary>
		public string getSessionIDFromPageID(string strPageID)
		{
            string strSessionID = "";
            SQLiteDataReader reader = null;
            try
            {
                string strSQL = "SELECT SessionID " +
                                "FROM Pages " +
                                "WHERE ID = " + QN(strPageID);
                reader = execute(m_Connection, strSQL);
                if (reader.Read() == true)
                {
                    strSessionID = reader.GetString(0);
                }
            }
            catch (Exception ex)
            {
                Logger.log(ex.Message, Logger.Level.WARN);
            }
            finally
            {
                if (reader != null) reader.Close();
            }

            return strSessionID;
		}

        #endregion

        #region Handlers for events received from the 'model'

        /// <summary>
        /// Called when a new session has been created. We add it to the DB.
        /// </summary>
        private void onNewSession(object sender, Session.Args e)
        {
            // If the event originated from the DB, we don't need to process it...
            if (e.ActionOrigin.OriginType == ActionOrigin.OriginTypeEnum.DB_LOAD)
            {
                return;
            }

            // We get data from the session...
            Session session = (Session)sender;
            string strSessionID = session.ID.ToString();
            double dCreationDateTime = session.CreationDateTime.ToOADate();

            // And add it to the DB...
            string strSQL = "INSERT INTO Sessions " +
                            "(ID, CreationDateTime, Name, Tag) " +
                            "VALUES " +
                            "(" +
                            QC(strSessionID) +
                            QC(dCreationDateTime) +
                            QC(session.Name) +
                            QN(session.Tag) +
                            ")";

            // We queue the action to be processed by the worker thread...
            QueuedAction action = new QueuedAction();
            action.SQLStatements.Add(strSQL);
            m_ThreadPool.QueueWorkItem(processQueuedNonQuery, action);
        }

        /// <summary>
        /// Called when a page is added to the session.
        /// </summary>
        private void onPageAdded(object sender, Session.Args e)
        {
            // If the event originated from the DB, we don't need to process it...
            if (e.ActionOrigin.OriginType == ActionOrigin.OriginTypeEnum.DB_LOAD)
            {
                return;
            }

            // We get data from the session...
            Session session = (Session)sender;
            string strSessionID = session.ID.ToString();

            // We get data from the page...
            string strPageID = e.Page.ID.ToString();
            double dCreationDateTime = e.Page.CreationDateTime.ToOADate();

            // And add it to the DB...
            string strSQL = "INSERT INTO Pages " +
                            "(ID, SessionID, CreationDateTime, Name, Tag) " +
                            "VALUES " +
                            "(" +
                            QC(strPageID) +
                            QC(strSessionID) +
                            QC(dCreationDateTime) +
                            QC(e.Page.Name) +
                            QN(e.Page.Tag) +
                            ")";

            // We queue the action to be processed by the worker thread...
            QueuedAction action = new QueuedAction();
            action.SQLStatements.Add(strSQL);
            m_ThreadPool.QueueWorkItem(processQueuedNonQuery, action);
        }

        /// <summary>
        /// Called when a page has been removed. We delete it from the DB.
        /// </summary>
        private void onPageRemoved(object sender, Session.Args e)
        {
            // If the event originated from the DB, we don't need to process it...
            if (e.ActionOrigin.OriginType == ActionOrigin.OriginTypeEnum.DB_LOAD)
            {
                return;
            }

            string strPageID = e.Page.ID.ToString();

            QueuedAction action = new QueuedAction();

            // We remove all shapes from the page...
            string strSQL = "DELETE FROM Shapes WHERE PageID=" + QN(strPageID);
            action.SQLStatements.Add(strSQL);

            // And we remove the page itself...
            strSQL = "DELETE FROM Pages WHERE ID=" + QN(strPageID);
            action.SQLStatements.Add(strSQL);

            // We add the action to the queue processed by the worker thread...
            m_ThreadPool.QueueWorkItem(processQueuedNonQuery, action);
        }

        /// <summary>
        /// Called when a shape edit has completed. We store the shape in the DB.
        /// </summary>
        private void onStoreShape(object sender, Session.Args e)
        {
            // If the event originated from the DB, we don't need to process it...
            if (e.ActionOrigin.OriginType == ActionOrigin.OriginTypeEnum.DB_LOAD)
            {
                return;
            }

            // We check if we should be storing this shape...
            if (isShapeStorable(e.Shape) == false)
            {
                return;
            }

            // We create the action to store the shape and add it to the queue...
            QueuedAction action = new QueuedAction();
            action.Shape = e.Shape;
            action.PageID = e.Page.ID;
            m_ThreadPool.QueueWorkItem(processQueuedShapeUpdate, action);
        }

        /// <summary>
        /// Called when a shape has been removed from a page.
        /// </summary>
        private void onRemoveShape(object sender, Session.Args e)
        {
            // If the event originated from the DB, we don't need to process it...
            if (e.ActionOrigin.OriginType == ActionOrigin.OriginTypeEnum.DB_LOAD)
            {
                return;
            }

            // We create the SQL to remove the shape from the page...
            string strShapeID = e.Shape.ID.ToString();
            string strSQL = "DELETE FROM Shapes WHERE ID=" + QN(strShapeID);

            // And we queue it up to be processed on the worker thread...
            QueuedAction action = new QueuedAction();
            action.SQLStatements.Add(strSQL);
            m_ThreadPool.QueueWorkItem(processQueuedNonQuery, action);
        }

        /// <summary>
        /// Returns true if the shape should be stored, false otherwise.
        /// </summary>
        private bool isShapeStorable(Shape shape)
        {
            //  Most shapes should be stored, but some should not...
            if (shape.GetType() == typeof(Shape_Pointer))
            {
                return false;
            }
            return true;
        }

        #endregion

        #region DB and table creation

        /// <summary>
        /// We create the DB if it does not already exist.
        /// </summary>
        private void createDB()
        {
            // Does the DB file already exist? 
            if (File.Exists(DB_FILENAME) == true)
            {
                return;
            }

            // We open the DB...
            string strConnectionString = getDBConnectionString();
            SQLiteConnection connection = new SQLiteConnection(strConnectionString);
            connection.Open();

            // We create the tables...
            createTable_Sessions(connection);
            createTable_Pages(connection);
            createTable_Shapes(connection);

            connection.Close();
        }

        /// <summary>
        /// Returns the connection string for the DB.
        /// </summary>
        private string getDBConnectionString()
        {
            return "Data Source=" + DB_FILENAME + ";Version=3";
        }

        /// <summary>
        /// Creates the 'Sessions' table. This holds data about Session
        /// object (from the CNShapes library).
        /// </summary>
        private void createTable_Sessions(SQLiteConnection connection)
        {
            // We create the table...
            SQLiteCommand command = new SQLiteCommand(connection);
            command.CommandText = "CREATE TABLE Sessions " +
                                  "(" +
                                  "ID STRING PRIMARY KEY, " +
                                  "CreationDateTime DOUBLE, " +
                                  "Name STRING, " +
                                  "Tag STRING" +
                                  ")";
            command.ExecuteNonQuery();

            // We add an index on the creation date/time as we will need 
            // to order by this when retrieving data...
            command.CommandText = "CREATE INDEX idx_Sessions_CreationDateTime ON Sessions (CreationDateTime)";
            command.ExecuteNonQuery();
        }

        /// <summary>
        /// Creates the 'Pages' table. This holds data from the Page
        /// object.
        /// </summary>
        private void createTable_Pages(SQLiteConnection connection)
        {
            // We create the table...
            SQLiteCommand command = new SQLiteCommand(connection);
            command.CommandText = "CREATE TABLE Pages " +
                                  "(" +
                                  "ID STRING PRIMARY KEY, " +
                                  "SessionID STRING, " +
                                  "CreationDateTime DOUBLE, " +
                                  "Name STRING, " +
                                  "Tag STRING" +
                                  ")";
            command.ExecuteNonQuery();

            // We create an index on the SessionID so that we can quickly look up
            // all pages for a given session...
            command.CommandText = "CREATE INDEX idx_Pages_SessionID ON Pages (SessionID)";
            command.ExecuteNonQuery();

            // We add an index on the creation date/time as we will need 
            // to order by this when retrieving data...
            command.CommandText = "CREATE INDEX idx_Pages_CreationDateTime ON Pages (CreationDateTime)";
            command.ExecuteNonQuery();
        }

        /// <summary>
        /// Creates the Shapes table. This holds data from objects derived from
        /// the Shape class in the CNShapes library. 
        /// </summary><remarks>
        /// As there are different types of Shape, the data for the the shapes is 
        /// stored as a BLOB. (This is in the same format as Shape data that is
        /// sent across the network.)
        /// </remarks>
        private void createTable_Shapes(SQLiteConnection connection)
        {
            // We create the table...
            SQLiteCommand command = new SQLiteCommand(connection);
            command.CommandText = "CREATE TABLE Shapes " +
                                  "(" +
                                  "ID STRING PRIMARY KEY, " +
                                  "PageID STRING, " +
                                  "ShapeData BLOB" +
                                  ")";
            command.ExecuteNonQuery();

            // We create an index on the PageID so that we can quickly find all
            // shapes for a given page...
            command.CommandText = "CREATE INDEX idx_Shapes_PageID ON Shapes (PageID)";
            command.ExecuteNonQuery();
        }

        #endregion

        #region Private functions called on the worker thread

        /// <summary>
        /// Called on the worker thread to update a Shape in the Shapes table.
        /// </summary>
        private object processQueuedShapeUpdate(object state)
        {
            QueuedAction action = (QueuedAction)state;

            // We get the data for the shape...
            string strShapeID = action.Shape.ID.ToString();
            string strPageID = action.PageID.ToString();
            byte[] shapeData = StreamHelper.convertToByteArray(action.Shape);

            // We remove any existing data for the shape...
            string strSQL = "DELETE FROM Shapes WHERE ID=" + QN(strShapeID);
            executeNonQuery(m_WorkerThreadConnection, strSQL);

            // We add the new shape...
            strSQL = "INSERT INTO Shapes " +
                     "(ID, PageID, ShapeData) " +
                     "VALUES " +
                     "(" +
                     QC(strShapeID) +
                     QC(strPageID) +
                     "@shapeData" +
                     ")";
            SQLiteCommand command = new SQLiteCommand(strSQL, m_WorkerThreadConnection);
            command.Parameters.Add("@shapeData", System.Data.DbType.Binary, shapeData.Length).Value = shapeData;
			try
			{
				command.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
                Logger.log(ex.Message, Logger.Level.WARN);
			}

            return null;
        }

        /// <summary>
        /// Called on the worker thread to process a set of non-query 
        /// SQL statements.
        /// </summary>
        private object processQueuedNonQuery(object state)
        {
            QueuedAction action = (QueuedAction)state;
            foreach (string strSQLStatement in action.SQLStatements)
            {
                executeNonQuery(m_WorkerThreadConnection, strSQLStatement);
            }

            return null;
        }

        /// <summary>
        /// Called on the worker thread to load shapes for the page passed in.
        /// </summary>
        private object processLoadShapesAction(object state)
        {
            SQLiteDataReader reader = null;
            try
            {
                // We load the shapes for the page passed in...
                QueuedAction action = (QueuedAction)state;
                Page page = action.Page;
                string strPageID = page.ID.ToString();
                string strSQL = "SELECT ID, ShapeData FROM Shapes WHERE PageID=" + QN(strPageID);
                reader = execute(m_WorkerThreadConnection, strSQL);
                while (reader.Read() == true)
                {
                    string strID = reader.GetString(0);
                    byte[] shapeData = readBLOB(reader, 1);

                    // We create the Shape object from the data and add it to the page...
                    Shape shape = StreamHelper.convertFromByteArray<Shape>(shapeData);
                    page.addShape(shape, getActionOrigin());
                }
            }
            catch (Exception ex)
            {
                Logger.log(ex.Message, Logger.Level.WARN);
            }
            finally
            {
                if (reader != null) reader.Close();
            }

            return null;
        }

        #endregion

        #region Private functions

        /// <summary>
        /// Constructor. (Private for singleton.)
        /// </summary>
        private DBStorage()
        {
        }

        /// <summary>
        /// Reads the session with the ID passed in into the current session.
        /// </summary>
        private void readSession(string strSessionID)
        {
            SQLiteDataReader reader = null;
            try
            {
                // We start a new session...
                Session session = Session.getInstance();
                session.newSession(new Guid(strSessionID), getActionOrigin());

                // We load the session data and setup the session from it...
                string strSQL = "SELECT CreationDateTime, Name, Tag " +
                                "FROM Sessions " +
                                "WHERE ID = " + QN(strSessionID);
                reader = execute(m_Connection, strSQL);
                if (reader.Read() == false)
                {
                    throw new Exception("Session not found in DB. ID=" + strSessionID);
                }
                double dCreationDateTime = reader.GetDouble(0);
                session.CreationDateTime = DateTime.FromOADate(dCreationDateTime);
                session.Name = reader.GetString(1);
                session.Tag = reader.GetString(2);
            }
            catch (Exception ex)
            {
                Logger.log(ex.Message, Logger.Level.WARN);
            }
            finally
            {
                if (reader != null) reader.Close();
            }
        }

        /// <summary>
        /// We load the pages for the session passed in and add them to
        /// the active session. (This assumes that the active session has
        /// already been set to be the correct session for these pages.)
        /// </summary>
        private void loadPagesIntoSession(string strSessionID)
        {
            SQLiteDataReader reader = null;
            try
            {
                Session session = Session.getInstance();
                string strSQL = "SELECT ID, CreationDateTime, Name, Tag " +
                                "FROM Pages " +
                                "WHERE SessionID = " + QN(strSessionID);
                reader = execute(m_Connection, strSQL);
                while (reader.Read() == true)
                {
                    string strPageID = reader.GetString(0);
                    Page page = session.addPage(new Guid(strPageID), getActionOrigin());
                    page.CreationDateTime = DateTime.FromOADate(reader.GetDouble(1));
                    page.Name = reader.GetString(2);
                    page.Tag = reader.GetString(3);
                }

                // We load the shapes for each page...
                foreach (Page page in session.Pages)
                {
                    loadShapesAsync(page);
                }
            }
            catch (Exception ex)
            {
                Logger.log(ex.Message, Logger.Level.WARN);
            }
            finally
            {
                if (reader != null) reader.Close();
            }
        }
    
        /// <summary>
        /// Reads a BLOB from the field index passed in.
        /// </summary>
        byte[] readBLOB(SQLiteDataReader reader, int iField)
        {
            // We read the blob in chunks and add it to a stream...
            const int BUFFER_SIZE = 1024;
            byte[] buffer = new byte[BUFFER_SIZE];
            MemoryStream stream = new MemoryStream();

            long bytesRead = 0;
            long offset = 0;

            while ((bytesRead = reader.GetBytes(iField, offset, buffer, 0, BUFFER_SIZE)) > 0)
            {
                stream.Write(buffer, 0, (int)bytesRead);
                offset += bytesRead;
            }

            // We get the byte array from the stream and return it...
            byte[] result = stream.ToArray();
            stream.Close();

            return result;
        }

        /// <summary>
        /// Executes the SQL passed in.
        /// </summary>
        private void executeNonQuery(SQLiteConnection connection, string strSQL)
        {
            try
            {
                SQLiteCommand command = new SQLiteCommand(strSQL, connection);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                // We are silently catching SQL errors here, as we can sometimes
                // try to store an item that already exists in the DB. We may want
                // to think about a better way of doing this...
                Logger.log(ex.Message, Logger.Level.NOTICE);
            }
        }

        /// <summary>
        /// Executes the query passed in and returns the reader object.
        /// </summary>
        /// <returns></returns>
        private SQLiteDataReader execute(SQLiteConnection connection, string strQuery)
        {
            SQLiteDataReader result = null;
            try
            {
                SQLiteCommand command = new SQLiteCommand(strQuery, connection);
                result = command.ExecuteReader();
            }
            catch (Exception ex)
            {
                Logger.log(ex.Message, Logger.Level.WARN);
            }
            return result;
        }

        /// <summary>
        /// Returns the value in a format to be inserted into the DB with a 
        /// comma after it.
        /// </summary>
        private string QC(string strValue)
        {
            return "'" + strValue + "', ";
        }
        private string QC(double dValue)
        {
            return dValue.ToString() + ", ";
        }

        /// <summary>
        /// Returns the value in a format to be inserted into the DB but without a 
        /// comma after it.
        /// </summary>
        private string QN(string strValue)
        {
            return "'" + strValue + "'";
        }
        private string QN(double dValue)
        {
            return dValue.ToString();
        }

        /// <summary>
        /// Removes empty pages and sessions from the DB.
        /// </summary>
        private void removeEmptyPages()
        {
            // TODO: Write this!
        }

        /// <summary>
        /// Returns an ActionOrigin indicating that an action originated
        /// from a DB load.
        /// </summary>
        private ActionOrigin getActionOrigin()
        {
            return new ActionOrigin { OriginType = ActionOrigin.OriginTypeEnum.DB_LOAD };
        }

        #endregion

        #region Private data

        private const string DB_FILENAME = "SessionDB.db";

        // The singleton instance...
        private static DBStorage m_Instance = null;

        // We have two DB connectrions. One used by the main thread, and
        // another 'cloned' copy used by the background thread...
        private SQLiteConnection m_Connection = null;
        private SQLiteConnection m_WorkerThreadConnection = null;

        // This thread-pool lets us process DB requests and updates on a background thread.
        // Despite being a 'pool' it just manages one thread, as we want to make sure that
        // queued updates are applied to the DB in the order they were added to the queue...
        private const int THREAD_RECOVERY_TIMEOUT = 10000;
        private const int WAIT_FOR_IDLE_TIMEOUT = 5000;
        private SmartThreadPool m_ThreadPool = new SmartThreadPool(THREAD_RECOVERY_TIMEOUT, 1);

        #endregion

    }
}
